﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_ProjectPoleTopTypes')
	BEGIN
		DROP PROCEDURE up_Report_ProjectPoleTopTypes
	END

GO

CREATE PROCEDURE up_Report_ProjectPoleTopTypes
	@ProjectId int
AS


DECLARE @ProjectType int
SELECT @ProjectType = ProjectTypeFk FROM tb_Vendor_Projects WHERE Vendor_ProjectId = @ProjectId

-- Project Type 1 is Xmission Inspection Visit
IF (@ProjectType = 1)
BEGIN
	SELECT
		TopType.[Name] as PoleTop,
		COUNT(*) as VisitCount
	FROM tb_Vendor_InspVisit_XmissionStructures_Poles Pole
	INNER JOIN tb_XMissionStructures_TopTypes TopType
		ON TopType.XMissionStructures_TopTypeId = Pole.TopTypeFk
	WHERE
		Pole.Vendor_ProjectFK = @ProjectId
	GROUP BY Pole.TopTypeFk, TopType.[Name]
	ORDER BY TopType.[Name]
END

-- Project Type 2 is Xmission GPS Visit
IF (@ProjectType = 2)
BEGIN
	--Transmission Poles GPS Visits by Pole Top
	SELECT
		TopType.[Name] as PoleTop,
		COUNT(*) as VisitCount
	FROM tb_Vendor_Visit_XMissionStructures_Poles Pole
	INNER JOIN tb_Vendor_Visit_XMissionStructures Structure 
		ON Pole.Vendor_Visit_XMissionStructureFK = Structure.Vendor_Visit_XMissionStructureId
	INNER JOIN tb_XMissionStructures_TopTypes TopType 
		ON TopType.XMissionStructures_TopTypeId = Pole.XMissionStructures_PoleTopTypeFk
	WHERE 
		Structure.Vendor_ProjectFK = @ProjectId
	GROUP BY Pole.XMissionStructures_PoleTopTypeFk, TopType.[Name]
	ORDER BY TopType.[Name]
END


-- Project Type 3 is Distribution Inspection Visit
IF (@ProjectType = 3)
BEGIN
	SELECT
		TopType.[Name] as PoleTop,
		COUNT(*) as VisitCount
	FROM tb_Vendor_Distribution_Poles Pole
	INNER JOIN tb_Vendor_Distribution_TopsXPole XPole
		ON XPole.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
	INNER JOIN tb_Distributions_TopTypes TopType
		ON TopType.Distributions_TopTypeId = XPole.Vendor_Distribution_TopFk
	WHERE
		Pole.ProjectFK = @ProjectId
	GROUP BY TopType.Distributions_TopTypeId, TopType.[Name]
	ORDER BY TopType.[Name]
END


-- Project Type 4 is Distribution Gps Visit
	
	
	

GO

GRANT EXEC ON up_Report_ProjectPoleTopTypes TO PPIReports

GO

